package services

import (
	"database/sql"
	"fmt"
	"log"
	"time"
	"your_project/backend/models" // 请替换为你的实际模块路径

	_ "github.com/mattn/go-sqlite3"
)

type DatabaseService struct {
	db *sql.DB
}

// NewDatabaseService 初始化数据库连接并创建表
func NewDatabaseService(dbPath string) (*DatabaseService, error) {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, err
	}

	// 创建记账表格
	createTableSQL := `
    CREATE TABLE IF NOT EXISTS transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        type TEXT NOT NULL,
        category TEXT NOT NULL,
        amount REAL NOT NULL,
        description TEXT
    );`
	_, err = db.Exec(createTableSQL)
	if err != nil {
		return nil, fmt.Errorf("failed to create table: %v", err)
	}

	log.Println("Database connected and table ensured")
	return &DatabaseService{db: db}, nil
}

// AddTransaction 添加新的交易记录
func (s *DatabaseService) AddTransaction(t models.Transaction) error {
	query := `INSERT INTO transactions (date, type, category, amount, description) VALUES (?, ?, ?, ?, ?)`
	_, err := s.db.Exec(query, t.Date.Format(time.RFC3339), t.Type, t.Category, t.Amount, t.Description)
	return err
}

// GetMonthlyTransactions 获取指定年份和月份的所有交易记录
func (s *DatabaseService) GetMonthlyTransactions(year int, month time.Month) ([]models.Transaction, error) {
	startDate := time.Date(year, month, 1, 0, 0, 0, 0, time.UTC)
	endDate := startDate.AddDate(0, 1, 0) // 下个月的第一天

	query := `SELECT id, date, type, category, amount, description FROM transactions 
              WHERE date >= ? AND date < ? ORDER BY date DESC`
	rows, err := s.db.Query(query, startDate.Format(time.RFC3339), endDate.Format(time.RFC3339))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var transactions []models.Transaction
	for rows.Next() {
		var t models.Transaction
		var dateStr string
		err := rows.Scan(&t.ID, &dateStr, &t.Type, &t.Category, &t.Amount, &t.Description)
		if err != nil {
			return nil, err
		}
		t.Date, err = time.Parse(time.RFC3339, dateStr)
		if err != nil {
			return nil, err
		}
		transactions = append(transactions, t)
	}
	return transactions, nil
}

// CalculateMonthlySum 计算指定月份的消费总和
func (s *DatabaseService) CalculateMonthlySum(year int, month time.Month) (float64, error) {
	startDate := time.Date(year, month, 1, 0, 0, 0, 0, time.UTC)
	endDate := startDate.AddDate(0, 1, 0)

	query := `SELECT COALESCE(SUM(amount), 0) FROM transactions 
              WHERE type = 'expense' AND date >= ? AND date < ?`
	row := s.db.QueryRow(query, startDate.Format(time.RFC3339), endDate.Format(time.RFC3339))

	var total float64
	err := row.Scan(&total)
	return total, err
}
